/*
 * PhysicalDAO.java
 *
 * Created on March 26, 2007, 10:53 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */
 
package edu.byu.isys413.jjenkins.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

/**
 * This class contains methods to create, read, update and delete the
 * Physical Object. It also contains methods to read an object by
 * serveral different criteria. All interaction between the object
 * and the database is done through this class.
 *
 * @author      Josh
 * @version     1.0
 *
 */
public class PhysicalDAO extends ProductDAO{
    
    private static PhysicalDAO instance = null;
    
    /** Creates a new instance of PhysicalDAO */
    PhysicalDAO() {
    }
    
    /**This method implements the singleton pattern. It insures that only
     * on instance of PhysicalDAO exists
     *
     * @return: PhysicalDAO instance */
    
    public static synchronized PhysicalDAO getInstance() {
        if (instance == null) {
            instance = new PhysicalDAO();
        }
        return instance;
    }
    
    /** This method creates a new PhysicalBO with a unique QUID, puts the interest
     * in cache, and then returns it to the calling method.
     *
     * @return: PhysicalBO
     * @throws: DataException
     */
    
    public ProductBO create() throws DataException {
        try {
            
            String id = GUID.generate();
            ProductBO object = new PhysicalBO();
            object.setId(id);
            Cache.getInstance().put(object.getId(), object);
            return object;
        } catch (Exception e) {
            throw new DataException("Could not create GUID", e);
        }
        
    }
    
    
    /**
     * This is the public read statement.  It loads an existing record
     * from the database.
     *
     * @Par: String: id
     * @Return: PhysicalBO
     * @Throws: DataException
     */
    public synchronized ProductBO read(String id) throws DataException {
        
        // Checks to see if the item is in cache
//        Cache cache = Cache.getInstance();
        PhysicalBO object = null;
        try {
            
            // Gets connection
            ConnectionPool connPool = ConnectionPool.getInstance();
            Connection conn = connPool.get();
            
            // If the item is not in cache a connection is checked out and
            // The item is read from the database.
            if(object == null){
                try {
                    //reads in object
                    object = (PhysicalBO)read(id, conn);
                    
                }catch (SQLException e) {
                    conn.rollback();
                    throw new DataException("Could not retrieve record for id=" + id, e);
                }
            }
            
            // connection committs and is returned.
            conn.commit();
            connPool.release(conn);
            
            
            
        } catch (SQLException e) {
            throw new DataException("Could not retrieve record for id=" + id, e);
        } catch (DataException e) {
            throw new DataException("Could not retrieve record for id=" + id, e);
        } catch (ConnectionPoolException e) {
            throw new DataException("Could not retrieve record for id=" + id, e);
        }
        
        // Return object
        return object;
    }
    
    /**
     *  This is a package method that is called by the public read (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     *
     *  @Par: String: id, Connection conn
     *  @Return: PhysicalBO
     *  @throws: DataException, SQLException
     */
    synchronized ProductBO read(String id, Connection conn) throws SQLException, DataException {
        
        
        // Checks to see if the item is in cache
//        Cache cache = Cache.getInstance();
        PhysicalBO object = null;
        
        // Queries the database
        if(object == null){
            
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM physical WHERE product_id = ?");
            pstmt.setString(1, id);
            pstmt.execute();
            ResultSet rs = pstmt.getResultSet();
            
            if (rs.next()) {
                object = new PhysicalBO();
                object.setId(rs.getString("product_id"));
                object.setSerialNum(rs.getString("serial_number"));
                object.setShelfLocation(rs.getString("shelf_location"));
                object.setDatePurchased(rs.getDate("date_purchased"));
                object.setType(rs.getString("product_type"));
                object.setStore(StoreDAO.getInstance().read(rs.getString("store_id"), conn));
                object.setConceptual(ConceptualDAO.getInstance().read(rs.getString("conceptual_id"), conn));
                
                
                
                // Sets already in database
                object.setObjectAlreadyInDB(true);
                
                // adds to cache
                //cache.put(object.getId(), object);
                
            }else{
                throw new DataException("Object was not found in the database.");
            }
        }
        
        
        // returns object
        return object;
    }
    
    /**
     * This is the public save method.  It is what is called when
     * the user (controller) code wants to save or update an object
     * into the database.
     *
     * @par PhysicalBO object
     * @throws DataExcpetion
     */
    public synchronized void save(ProductBO object) throws DataException {
        try {
            
            
            // retrieve a database connection from the pool
            ConnectionPool connPool = ConnectionPool.getInstance();
            
            Connection conn = connPool.get();
            
            
            try {
                
                // Saves Interest
                save(object, conn);
                
            }catch (SQLException e) {
                conn.rollback();
                e.printStackTrace();
                throw new DataException("Could not save record for" + object.getId(), e);
            }
            
            // connection committs and is returned.
            conn.commit();
            connPool.release(conn);
            
            // touch the cache for the object
            Cache.getInstance().touch(object.getId());
            
            
            
        } catch (DataException e) {
            throw new DataException("Could not save record for" + object.getId(), e);
        } catch (ConnectionPoolException e) {
            throw new DataException("Could not save record for" + object.getId(), e);
        } catch (SQLException e) {
            throw new DataException("Could not save record for" + object.getId(), e);
        }
        
        
    }
    
    /**
     *  This is a package method that is called by the public save (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     *
     *  By having other DAOs call this save method (rather than update or
     *  insert below, each DAO in a chained save (like the CustomerDAO calling
     *  MembershipDAO calling InterestDAO save chain) can independently decide
     *  whether to udpate or insert the BO it's saving.  That's why I made
     *  update and insert private rather than package level -- if you call
     *  them directly from another DAO, this DAO can't decide whether it's
     *  object needs to be inserted or updated.
     *
     *  @par: PhysicalBO, Connection
     *  @throws: SQLException, DataException
     */
    synchronized void save(ProductBO object, Connection conn) throws SQLException, DataException {
        
        // check the dirty flag in the object.  if it is dirty,
        // run update or insert
        if (object.isDirty()) {
            if (object.isObjectAlreadyInDB()) {
                this.update((PhysicalBO)object, conn);
            }else{
                this.insert((PhysicalBO)object, conn);
            }
            object.setDirty(false);
        }
        
        
        
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  But
     * logically, it's really just a part of save.
     *
     * @Par PhysicalBO object
     */
    private synchronized void update(PhysicalBO object, Connection conn) throws SQLException, DataException {
        
        PreparedStatement pstmt = conn.prepareStatement("UPDATE physical SET product_id = ?, serial_number = ?, shelf_location = ?, date_purchased = ?, product_type = ?, store_id = ?, conceptual_id = ? WHERE product_id = ?");
        pstmt.setString(1, object.getId());
        pstmt.setString(2, object.getSerialNum());
        pstmt.setString(3, object.getShelfLocation());
        pstmt.setDate(4, object.getDatePurchased());
        pstmt.setString(5, object.getType());
        pstmt.setString(6, object.getStore().getId());
        pstmt.setString(7, object.getConceptual().getId());
        pstmt.setString(8, object.getId());
        
        pstmt.execute();
        
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL insert statement and make it more readable.  But
     * logically, it's really just a part of save.
     *
     * @Par PhysicalBO object
     */
    private synchronized void insert(PhysicalBO object, Connection conn) throws SQLException, DataException {
        
        // Creates prepared statemetn
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO physical VALUES(?, ?, ?, ?, ?, ?, ?)");
        pstmt.setString(1, object.getId());
        pstmt.setString(2, object.getSerialNum());
        pstmt.setString(3, object.getShelfLocation());
        pstmt.setDate(4, object.getDatePurchased());
        pstmt.setString(5, object.getType());
        pstmt.setString(6, object.getStore().getId());
        pstmt.setString(7, object.getConceptual().getId());
        
        //Executes prepated statement
        pstmt.execute();
        
        //Sets already in database true
        object.setObjectAlreadyInDB(true);
        
    }
    
    
    /** We intentionally do not allow a PhysicalBO object to be deleted because
     * it could mess up relationships in the database. Obmitting the delete
     * also prevents fraud. */
    
    public synchronized void delete(PhysicalBO customer)throws DataException{
    /* We intentionally do not allow a PhysicalBO object to be deleted because
     * it could mess up relationships in the database. Obmitting the delete
     * also prevents fraud. */
        throw new DataException("You can not delete a PhysicalBO");
    }
    
    public synchronized List readAll() throws SQLException, DataException {
        
        List list = new LinkedList();
        
        try {
            
            // Gets connection
            ConnectionPool connPool = ConnectionPool.getInstance();
            Connection conn = connPool.get();
            
            // prepares statemnt
            
            PreparedStatement pstmt = conn.prepareStatement("SELECT product_id FROM physical");
            pstmt.execute();
            ResultSet rs = pstmt.getResultSet();
            
            
            while(rs.next()){
                String line_id = rs.getString("product_id");
                list.add(read(line_id, conn));
            }
            
            // connection committs and is returned.
            conn.commit();
            connPool.release(conn);
            
            
            
        } catch (SQLException e) {
            throw new DataException("Could not retrieve record", e);
        } catch (DataException e) {
            e.printStackTrace();
            throw new DataException("Could not retrieve record", e);
        } catch (ConnectionPoolException e) {
            throw new DataException("Could not retrieve record", e);
        }
        
        
        
        return list;
    }
    
    /**
     * This method queries that database for a list of all physical products in a certain
     * store. It adds these objects to a list and puts them in cache
     *
     *&return: Physical product
     */
    public PhysicalBO getBySerialNum(String serial) throws DataException {
        PhysicalBO object = new PhysicalBO();
        
        try {
            
            // Gets connection
            ConnectionPool connPool = ConnectionPool.getInstance();
            Connection conn = connPool.get();
            
            // Creates statement and populates list
            try {
                
                // prepares statemnt
                PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM physical WHERE serial_number = ?");
                pstmt.setString(1, serial);
                pstmt.execute();
                ResultSet rs = pstmt.getResultSet();
                
                //get instance of cache to put objects in
                Cache cache = Cache.getInstance();
                
                if(rs.next()){
                    object.setId(rs.getString("product_id"));
                    object.setSerialNum(rs.getString("serial_number"));
                    object.setShelfLocation(rs.getString("shelf_location"));
                    object.setDatePurchased(rs.getDate("date_purchased"));
                    object.setType(rs.getString("product_type"));
                    object.setStore(StoreDAO.getInstance().read(rs.getString("store_id"), conn));
                    object.setConceptual(ConceptualDAO.getInstance().read(rs.getString("conceptual_id"), conn));
                    cache.put(object.getId(), object);
                    
                }
            }catch (SQLException e) {
                conn.rollback();
                throw new DataException("Could not retrieve list of objects " + e);
            }
            
            // After all items are read (customer, membership, interest) the
            // connection committs and is returned.
            conn.commit();
            connPool.release(conn);
            
            
            
        } catch (SQLException e) {
            throw new DataException("Could not retrieve list of objects " + e);
        } catch (DataException e) {
            throw new DataException("Could not retrieve list of objects " + e);
        } catch (ConnectionPoolException e) {
            throw new DataException("Could not retrieve list of objects " + e);
        }
        
        // Return object
        return object;
    }
    
    /**
     * This method queries that database for a list of all physical products in a certain
     * store. It adds these objects to a list and puts them in cache
     *
     *&return: list <of Physical products>
     */
    public List getByStoreID(String storeID) throws DataException {
        // List to store the string array of customers
        List list = new LinkedList();
        
        try {
            
            // Gets connection
            ConnectionPool connPool = ConnectionPool.getInstance();
            Connection conn = connPool.get();
            
            // Creates statement and populates list
            try {
                
                // prepares statemnt
                PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM physical WHERE store_id = ?");
                pstmt.setString(1, storeID);
                pstmt.execute();
                ResultSet rs = pstmt.getResultSet();
                
                //get instance of cache to put objects in
                Cache cache = Cache.getInstance();
                
                while(rs.next()){
                    
                    PhysicalBO object = new PhysicalBO();
                    object.setId(rs.getString("product_id"));
                    object.setSerialNum(rs.getString("serial_number"));
                    object.setShelfLocation(rs.getString("shelf_location"));
                    object.setDatePurchased(rs.getDate("date_purchased"));
                    object.setType(rs.getString("product_type"));
                    object.setStore(StoreDAO.getInstance().read(rs.getString("store_id"), conn));
                    object.setConceptual(ConceptualDAO.getInstance().read(rs.getString("conceptual_id"), conn));
                    list.add(object);
                    cache.put(object.getId(), object);
                    
                }
            }catch (SQLException e) {
                conn.rollback();
                throw new DataException("Could not retrieve list of objects " + e);
            }
            
            // After all items are read (customer, membership, interest) the
            // connection committs and is returned.
            conn.commit();
            connPool.release(conn);
            
            
            
        } catch (SQLException e) {
            throw new DataException("Could not retrieve list of objects " + e);
        } catch (DataException e) {
            throw new DataException("Could not retrieve list of objects " + e);
        } catch (ConnectionPoolException e) {
            throw new DataException("Could not retrieve list of objects " + e);
        }
        
        // Return object
        return list;
    }
    
    /**
     * This method queries that database for a list of all physical products in a certain
     * store. It adds these objects to a list and puts them in cache
     *
     *&return: list <of Physical products>
     */
    public List getByType(String type) throws DataException {
        // List to store the string array of customers
        List list = new LinkedList();
        
        try {
            
            // Gets connection
            ConnectionPool connPool = ConnectionPool.getInstance();
            Connection conn = connPool.get();
            
            // Creates statement and populates list
            try {
                
                // prepares statemnt
                PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM physical WHERE product_type = ?");
                pstmt.setString(1, type);
                pstmt.execute();
                ResultSet rs = pstmt.getResultSet();
                
                //get instance of cache to put objects in
                Cache cache = Cache.getInstance();
                
                while(rs.next()){
                    
                    PhysicalBO object = new PhysicalBO();
                    object.setId(rs.getString("product_id"));
                    object.setSerialNum(rs.getString("serial_number"));
                    object.setShelfLocation(rs.getString("shelf_location"));
                    object.setDatePurchased(rs.getDate("date_purchased"));
                    object.setType(rs.getString("product_type"));
                    object.setStore(StoreDAO.getInstance().read(rs.getString("store_id"), conn));
                    object.setConceptual(ConceptualDAO.getInstance().read(rs.getString("conceptual_id"), conn));
                    list.add(object);
                    cache.put(object.getId(), object);
                    
                }
            }catch (SQLException e) {
                conn.rollback();
                throw new DataException("Could not retrieve list of objects " + e);
            }
            
            // After all items are read (customer, membership, interest) the
            // connection committs and is returned.
            conn.commit();
            connPool.release(conn);
            
            
            
        } catch (SQLException e) {
            throw new DataException("Could not retrieve list of objects " + e);
        } catch (DataException e) {
            throw new DataException("Could not retrieve list of objects " + e);
        } catch (ConnectionPoolException e) {
            throw new DataException("Could not retrieve list of objects " + e);
        }
        
        // Return object
        return list;
    }
    
    /**
     * This method queries that database for a list of all physical products in a certain
     * store. It adds these objects to a list and puts them in cache
     *
     *&return: list <of Physical products>
     */
    public List getByKeyword(String key) throws DataException {
        // List to store the string array of customers
        List list = new LinkedList();
        
        try {
            
            // Gets connection
            ConnectionPool connPool = ConnectionPool.getInstance();
            Connection conn = connPool.get();
            
            // Creates statement and populates list
            try {
                String keyword = "%" + key + "%";
                // prepares statemnt
                PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM conceptual WHERE description like ?");
                pstmt.setString(1, keyword);
                pstmt.execute();
                ResultSet rs = pstmt.getResultSet();
                
                //get instance of cache to put objects in
                Cache cache = Cache.getInstance();
                
                while(rs.next()){
                    
                    ConceptualBO object = new ConceptualBO();
                    object.setId(rs.getString("product_id"));
                    String id = object.getId();
                    
                    PreparedStatement pstmt2 = conn.prepareStatement("SELECT * FROM physical WHERE conceptual_id = ?");
                    pstmt2.setString(1, id);
                    pstmt2.execute();
                    ResultSet rs2 = pstmt2.getResultSet();
                    
                    while(rs2.next()){
                        PhysicalBO object2 = new PhysicalBO();
                        object2.setId(rs.getString("product_id"));
                        object2.setSerialNum(rs.getString("serial_number"));
                        object2.setShelfLocation(rs.getString("shelf_location"));
                        object2.setDatePurchased(rs.getDate("date_purchased"));
                        object2.setType(rs.getString("product_type"));
                        object2.setStore(StoreDAO.getInstance().read(rs.getString("store_id"), conn));
                        object2.setConceptual(ConceptualDAO.getInstance().read(rs.getString("conceptual_id"), conn));
                        list.add(object2);
                        cache.put(object.getId(), object);
                    }
                    
                }
            }catch (SQLException e) {
                conn.rollback();
                throw new DataException("Could not retrieve list of objects " + e);
            }
            
            // After all items are read (customer, membership, interest) the
            // connection committs and is returned.
            conn.commit();
            connPool.release(conn);
            
            
            
        } catch (SQLException e) {
            throw new DataException("Could not retrieve list of objects " + e);
        } catch (DataException e) {
            throw new DataException("Could not retrieve list of objects " + e);
        } catch (ConnectionPoolException e) {
            throw new DataException("Could not retrieve list of objects " + e);
        }
        
        // Return object
        return list;
    }
}
